package com.kepler.dao.user;

import com.kepler.dao.BaseDao;
import com.kepler.pojo.User;
import com.mysql.jdbc.StringUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @author lenovo
 */
public class UserDaoImpl implements UserDao {

    public User getLoginUser(Connection connection, String userCode) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        if (connection != null){
            String sql = "select * from smbms_user where userCode = ?";
            Object[] params = {userCode};
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);

            if (resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("username"));
                user.setUserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setCreatedBy(resultSet.getInt("createdBy"));
                user.setCreationDate(resultSet.getDate("creationDate"));
                user.setModifyBy(resultSet.getInt("modifyBy"));
                user.setModifyDate(resultSet.getDate("modifyDate"));
            }

            // 查询成功后释放资源
            BaseDao.closeResources(null, preparedStatement, resultSet);
        }
        return user;
    }

    public int updatePassword(Connection connection, int id, String password) throws SQLException {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;
        if (connection != null) {
            String sql = "update smbms_user set userPassword = ? where id = ?;";
            Object[] params = {password, id};
            updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            BaseDao.closeResources(null, preparedStatement, null);
        }
        return updateRows;
    }

    public int getUserCount(Connection connection, String userName, int userRole) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            ArrayList<Object> list = new ArrayList<Object>();
            sql.append("select count(1) as count from smbms_user as u, smbms_role as r where u.userRole = r.id");

            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }

            if (userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }

            Object[] params = list.toArray();
            // 输出完整的 sql 语句方便调试
            System.out.println("UserDaoImpl->getUserCount" + sql.toString());
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
            if (resultSet.next()){
                count = resultSet.getInt("count");
            }
            BaseDao.closeResources(null, preparedStatement, resultSet);
        }
        return count;
    }

    public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Object> list = new ArrayList<Object>();
        List<User> userList = new ArrayList<User>();
        if (connection != null){
            StringBuffer sql = new StringBuffer();
            sql.append("select u.*, r.roleName as userRoleName from smbms_user as u, smbms_role as r where u.userRole = r.id");

            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userName like ?");
                list.add("%" + userName + "%");
            }

            if (userRole > 0){
                sql.append(" and u.userRole = ?");
                list.add(userRole);
            }
            sql.append(" order by creationDate DESC limit ?,?");
            // 在数据库中，分页使用 limit startpage, pagesize
            currentPageNo = (currentPageNo - 1) * pageSize;
            list.add(currentPageNo);
            list.add(pageSize);

            Object[] params = list.toArray();
            System.out.println("sql->getUserList:" + sql.toString());
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params);
            while (resultSet.next()){
                User _user = new User();
                _user.setId(resultSet.getInt("id"));
                _user.setUserCode(resultSet.getString("userCode"));
                _user.setUserName(resultSet.getString("userName"));
                _user.setGender(resultSet.getInt("gender"));
                _user.setBirthday(resultSet.getDate("birthday"));
                _user.setPhone(resultSet.getString("phone"));
                _user.setUserRole(resultSet.getInt("userRole"));
                _user.setUserRoleName(resultSet.getString("userRoleName"));
                userList.add(_user);
            }
            BaseDao.closeResources(null, preparedStatement, resultSet);
        }
        return userList;
    }

    public int addUser(Connection connection, User user) throws SQLException {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;
        if (connection != null && user != null){
            String sql = "insert into smbms_user (id, userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate, modifyBy, modifyDate) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
            Object[] params = {user.getId(), user.getUserCode(), user.getUserName(), user.getUserPassword(), user.getGender(), user.getBirthday(), user.getPhone(), user.getAddress(), user.getUserRole(), user.getCreatedBy(), user.getCreationDate(), user.getModifyBy(), user.getModifyDate()};
            updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            BaseDao.closeResources(null, preparedStatement, null);
        }
        return updateRows;
    }

    public int selectUserCodeExist(Connection connection, String userCode) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =  null;
        int count = 0;
        if (connection != null){
            String sql = "select count(1) as count from smbms_user where userCode = ?";
            Object[] params = {userCode};
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
            if (resultSet.next()) {
                count = resultSet.getInt("count");
                System.out.println("UserDaoImpl->selectUserCodeExist" + count);
            }
            BaseDao.closeResources(null, preparedStatement, resultSet);
        }
        return count;
    }

    public int deleteUser(Connection connection, int id) throws SQLException {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null) {
            String sql = "delete from smbms_user where id = ?";
            Object[] params = {id};
            updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            BaseDao.closeResources(null, preparedStatement, null);
        }

        return updateRows;
    }

    public int modify(Connection connection, User user) throws SQLException {
        PreparedStatement preparedStatement = null;
        int updateRows = 0;

        if (connection != null){
            String sql = "update smbms_user set userName = ?, gender = ?, birthday = ?, phone = ?, address = ?, userRole = ?, modifyBy = ?, modifyDate = ? where id = ?";
            Object[] params = {user.getUserName(), user.getGender(), user.getBirthday(), user.getPhone(), user.getAddress(), user.getUserRole(), user.getModifyBy(), user.getModifyDate(), user.getId()};
            updateRows = BaseDao.execute(connection, preparedStatement, sql, params);
            System.out.println("UserDaoImpl->modify->sql:" + sql);
        }

        return updateRows;
    }

    public User getUserById(Connection connection, int id) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = new User();

        if (connection != null) {
            String sql = "select u.*, r.roleName as userRoleName from smbms_user as u, smbms_role as r where u.userRole = r.id and u.id = ?";
            Object[] params = {id};
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, params);
            if (resultSet.next()) {
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setUserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setCreatedBy(resultSet.getInt("userRole"));
                user.setCreationDate(resultSet.getDate("creationDate"));
                user.setModifyBy(resultSet.getInt("modifyBy"));
                user.setModifyDate(resultSet.getDate("modifyDate"));
                user.setUserRoleName(resultSet.getString("userRoleName"));
            }
            BaseDao.closeResources(null, preparedStatement, resultSet);
        }
        return user;
    }

    @Test
    public void test(){
        ArrayList<Object> list = new ArrayList<Object>();
        list.add("'%"+"kepler"+"%'");
        list.add(1);
        System.out.println(Arrays.toString(list.toArray()));
    }
}
